﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using IHISCETeim.BLL;

using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;


namespace IHISCETeim.Web.Export.FTradeModel
{
    public partial class FTradeExportSKSJ : ExportPageBase
    {
        protected override string xlsTemplateFileName
        {
            get
            {
                return "应收帐款_财务部收款收据.xls";
            }
        }
        protected override string SheetName
        {
            get
            {
                return "收款收据";
            }
        }

        protected string DateFrom
        {
            get
            {
                DateTime dt = DateTime.Now; //当前时间
                return GetRequestValue<string>("DateFrom", DateTime.Now.ToString("yyyy-MM-01"));
            }
        }
        protected string DateTo
        {
            get
            {
                DateTime dt = Convert.ToDateTime(DateTime.Now.AddMonths(1).ToString("yyyy-MM-01")).AddDays(-1); //当前时间

                return GetRequestValue<string>("DateTo", dt.ToString("yyyy-MM-dd")) + " 23:59:59";

            }
        }
        /// <summary>
        /// 当前日期
        /// </summary>
        protected string CurrDate
        {
            get
            {
                return DateTime.Now.ToString("yyyy年MM月dd日");
            }
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            InitializeWorkbook();

            DataTableCollection dtc = FTradeReportController.FTradeExportGetSKSJ(DateFrom, DateTo);

            if (dtc[0].Rows.Count <= 0)
            {
                Response.Write(string.Format("从{0}到{1}没有任何收款收据记录,请确认", DateFrom, DateTo));
                Response.End();
                return;
            }


            DataTable sboagentlist = SBOController.GetAgentList();//所有经销商列表


            DataTable agentDT = dtc[0];
            DataTable CatheringDT = dtc[1];
            DataTable BalanceDT = dtc[2];
            //
            DataTable agentCatheringDT = new DataTable();
            DataTable agentBalanceDT = new DataTable();

            int sheetindex = 1;//从第二个工作表开始
            foreach (DataRow dr in agentDT.Rows)
            {
                string AliasName = dr["JingXiaoShang"].ToString();
                //经销商全称                
                DataRow[] drs = sboagentlist.Select(string.Format(" AliasName ='{0}'", AliasName.Trim()));
                string cardname = drs[0]["CardName"].ToString();

                agentCatheringDT = CatheringDT.Select(" JingXiaoShang='" + AliasName + "' ").CopyToDataTable();
                try
                {
                    agentBalanceDT = BalanceDT.Select(" JingXiaoShang='" + AliasName + "' ").CopyToDataTable();
                }
                catch (Exception ex) { }

                ISheet sheet2 = hssfworkbook.CloneSheet(0);
                hssfworkbook.SetSheetName(sheetindex, AliasName);

                WriteSheet(sheet2, agentCatheringDT, agentBalanceDT, cardname);
                sheet2.ForceFormulaRecalculation = true;
                sheetindex++;
            }

            //隐藏
            hssfworkbook.RemoveSheetAt(0);
            //
            WriteToFile();
        }

        private void WriteSheet(ISheet sheet2, DataTable agentCatheringDT, DataTable agentBalanceDT, string cardname)
        {
            //文本处理
            sheet2.GetRow(2).GetCell(0).SetCellValue(string.Format("致：{0}", cardname));
            sheet2.GetRow(5).GetCell(0).SetCellValue(string.Format("  一、 {0}收到贵公司以下用户货款：              单位:元", Convert.ToDateTime(DateTo).ToString("yyyy年MM月")));
            sheet2.GetRow(30).GetCell(7).SetCellValue(string.Format("{0}", DateTime.Now.ToString("yyyy-MM-dd")));
            //处理明细
            if (agentCatheringDT.Rows.Count > 3)
            {
                HSSFRow 源格式行 = (HSSFRow)sheet2.GetRow(8);
                MyInsertRow((HSSFSheet)sheet2, 9, agentCatheringDT.Rows.Count - 3, 源格式行);
            }
            int rowindexBase = 7;
            int rowindex = 0;
            int rowReal = rowindex + rowindexBase;
            foreach (DataRow dr in agentCatheringDT.Rows)
            {
                HSSFRow row1 = (HSSFRow)sheet2.GetRow(rowReal);
               // string Remark = string.IsNullOrEmpty(dr["Remark"].ToString().Trim()) ? dr["KuanXiangLeiXing"].ToString() : dr["Remark"].ToString();
                //Remark = Remark == "数据导入充帐处理" ? dr["KuanXiangLeiXing"].ToString() : Remark;
                //合并处理
                sheet2.AddMergedRegion(new CellRangeAddress(rowReal, rowReal, 2, 4));

                row1.GetCell(1).SetCellValue(rowindex + 1);
                row1.GetCell(2).SetCellValue(dr["MoneyFrom"].ToString());
                row1.GetCell(5).SetCellValue(Convert.ToDateTime( dr["ShoukuanRiQi"]));
                row1.GetCell(6).SetCellValue(Convert.ToDouble(dr["JinE"]));
                row1.GetCell(7).SetCellValue(Convert.ToDouble(dr["ShengYuJinE"]));
                row1.GetCell(8).SetCellValue(dr["Remark"].ToString());
                rowindex++;
                rowReal = rowindex + rowindexBase;
            }

            //
            rowindexBase = 14 + (agentCatheringDT.Rows.Count <= 3 ? 0 : agentCatheringDT.Rows.Count - 3);
            rowindex = 0;
            rowReal = rowindex + rowindexBase;

            //处理明细
            if (agentBalanceDT.Rows.Count > 9)
            {
                HSSFRow 源格式行 = (HSSFRow)sheet2.GetRow(rowReal);
                MyInsertRow((HSSFSheet)sheet2, rowReal + 1, agentBalanceDT.Rows.Count - 9, 源格式行);
            }


            foreach (DataRow dr in agentBalanceDT.Rows)
            {
                HSSFRow row1 = (HSSFRow)sheet2.GetRow(rowReal);
                //合并处理
                sheet2.AddMergedRegion(new CellRangeAddress(rowReal, rowReal, 2, 4));

                row1.GetCell(1).SetCellValue(rowindex + 1);
                row1.GetCell(2).SetCellValue(dr["HeTongHao"].ToString());
                row1.GetCell(5).SetCellValue(dr["XingHao"].ToString());
                row1.GetCell(6).SetCellValue(dr["JiHao"].ToString());
                row1.GetCell(7).SetCellValue(Convert.ToDouble(dr["BalanceMoney"]));

                rowindex++;
                rowReal = rowindex + rowindexBase;
            }
        }

        protected override string GetXlsFileName(string oldFileName)
        {
            //return base.GetXlsFileName(oldFileName);

            return string.Format("{0}{1}.xls", DateTime.Now.ToString("yyyy年MM月"), "收款收据汇总表").Trim();
        }
    }
}